package by.vdl.websort.server.dao.hibernate;

import by.vdl.websort.server.dao.BaseDao;
import by.vdl.websort.server.dao.StudyDao;
import by.vdl.websort.server.entity.CategoryDTO;
import by.vdl.websort.server.entity.ItemDTO;
import by.vdl.websort.server.entity.SortedTableDTO;
import by.vdl.websort.server.entity.StudyDTO;
import by.vdl.websort.server.entity.contextmodels.StudyUnsortedModel;
import org.hibernate.Query;
import org.hibernate.criterion.Restrictions;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.Iterator;
import java.util.List;

/**
 * User: zeor
 * Date: 16.11.13
 * Time: 11:53
 */
public class StudyDapImpl  extends BaseDao implements StudyDao{

    @Transactional
    public long addStudy(String study, long userId) throws Exception {
        StudyDTO studyDTO = new StudyDTO(-1, study, userId, new Date());
        sessionFactory.getCurrentSession().save(studyDTO);
        return studyDTO.getId();
    }

    @Transactional
    public long addCategory(String category, long studyId) throws Exception {

        CategoryDTO categoryDTO = new CategoryDTO(-1, category, studyId);
        sessionFactory.getCurrentSession().save(categoryDTO);
        return categoryDTO.getId();
    }

    @Transactional
    public long addItem(String item, long categoryId) throws Exception {
        ItemDTO itemDTO = new ItemDTO(-1, item, categoryId);
        sessionFactory.getCurrentSession().save(itemDTO);
        return itemDTO.getId();
    }

    @Transactional
    public StudyDTO getStudyByUserPassedId(long userPassedId) throws Exception {
        String sql = "select * from `studies` as study, `user_pass_study` as ups where ups.id=:userPassedId and study.id=ups.study_id";
        Query query = sessionFactory.getCurrentSession().createSQLQuery(sql).addEntity(StudyDTO.class);
        query.setParameter("userPassedId", userPassedId);
        return (StudyDTO)query.uniqueResult();
    }

    @Transactional
    public StudyDTO getStudyById(long studyId) throws Exception {
        return (StudyDTO)sessionFactory.getCurrentSession().createCriteria(StudyDTO.class)
                .add(Restrictions.eq("id", studyId)).uniqueResult();
    }

    @Transactional
    public CategoryDTO getCategoryById(long categoryId) throws Exception {
        return (CategoryDTO)sessionFactory.getCurrentSession().createCriteria(CategoryDTO.class)
                .add(Restrictions.eq("id", categoryId)).uniqueResult();
    }

    @Transactional
    public ItemDTO getItemById(long itemId) throws Exception {
        return (ItemDTO)sessionFactory.getCurrentSession().createCriteria(ItemDTO.class)
                .add(Restrictions.eq("id", itemId)).uniqueResult();
    }

    @Transactional
    public StudyUnsortedModel getUncompletedStudy(long studyId) throws Exception {
        String sql = "select s.id as studyId, s.name as studyName, s.user_id as userId, c.id as categoryId," +
                " c.name as categoryName, i.id as itemId, i.name as itemName from studies as  s, category as c," +
                " items as i where s.id =:studyId and c.study_id = s.id and i.study_id = s.id;";
        Query query = sessionFactory.getCurrentSession().createSQLQuery(sql);
        query.setParameter("studyId", studyId);

        List rs = query.list();

        StudyUnsortedModel studyUnsortedModel = new StudyUnsortedModel();
        boolean isFirstTime = true;
        StudyDTO studyDTO = null;
        for (Iterator it = rs.iterator(); it.hasNext();) {
            if(isFirstTime){
                studyDTO = new StudyDTO();
                /*studyDTO.setId(rs.getLong("studyId"));
                studyDTO.setName(rs.getString("studyName"));
                studyDTO.setUserId(rs.getLong("userId"));*/
                studyUnsortedModel.setStudy(studyDTO);
                isFirstTime = false;
            }
            CategoryDTO categoryDTO = new CategoryDTO();
            /*categoryDTO.setId(rs.getLong("categoryId"));
            categoryDTO.setName(rs.getString("categoryName"));
            categoryDTO.setStudyId(studyDTO.getId()); */

            if(!studyUnsortedModel.getCategories().contains(categoryDTO))
                studyUnsortedModel.getCategories().add(categoryDTO);

            ItemDTO itemDTO = new ItemDTO();
            /*itemDTO.setId(rs.getLong("itemId"));
            itemDTO.setName(rs.getString("itemName"));
            itemDTO.setStudyId(studyDTO.getId());*/

            if(!studyUnsortedModel.getItems().contains(itemDTO))
                studyUnsortedModel.getItems().add(itemDTO);
        }
        return studyUnsortedModel;
    }

    @Transactional
    public List<SortedTableDTO> getSortedTablesByPassedUser(long userPassedStudyId) throws Exception {
        return sessionFactory.getCurrentSession().createCriteria(SortedTableDTO.class)
                .add(Restrictions.eq("user_pass_study_id", userPassedStudyId)).list();
    }

    @Transactional
    public SortedTableDTO addSortedTable(long categoryId, long itemId, long userPassedStudyId) throws Exception {

        SortedTableDTO sortedTableDTO = new SortedTableDTO(categoryId, itemId, userPassedStudyId);

        sessionFactory.getCurrentSession().save(sortedTableDTO);

        return sortedTableDTO;
    }

    @Transactional
    public List<StudyDTO> listStudy() throws Exception {
        return sessionFactory.getCurrentSession().createCriteria(StudyDTO.class).list();
    }

    @Transactional
    public List<SortedTableDTO> getUnsortedDataSubmission(long studyId) throws Exception {

        String sql = "select * from category_item_links as cil where cil.category_id in " +
                "(select c.id from category as c where c.study_id = :study_id) order by cil.user_pass_study_id";
        Query query = sessionFactory.getCurrentSession().createSQLQuery(sql).addEntity(SortedTableDTO.class);
        query.setParameter("study_id", studyId);
        return (List<SortedTableDTO>)query.list();
    }
}
